package com.F05.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.F05.actionform.F0506ActionForm;
import com.F05.info.FInvoicingProductionInfo;
import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.util.CommonTools;

public class F0506DAO extends DataOperations{
	
	Logger log = Logger.getLogger(F0506DAO.class);
	public F0506DAO(){
		
	}
    public F0506DAO(Connection connection){
        super(connection);
    }

    public List queryTotalList(F0506ActionForm form) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select count(*) as countNum,sum(convert(float,unit_price)) as totalUnitPrice,sum(qty) as totalQty,sum(convert(float,total_rbm)) as totalRbm,");
    	sql.append(" sum(convert(float,total_usd)) as totalUsd, sum(convert(float,total_euros)) as totalEuros");
        sql.append(" from f_invoicing_production ");
        sql.append(" where 1=1 ");
 
    	if(!form.getContractNum1().equals("")){
    		sql.append(" and contract_num >="+form.getContractNum1());
    	}
    	if(!form.getContractNum2().equals("")){
    		sql.append(" and contract_num <="+form.getContractNum2());
    	}
    	if(!form.getInvoiceDate1().equals("")){
    		sql.append(" and invoice_date >= convert(datetime,'"+form.getInvoiceDate1()+"')");
    	}
    	if(!form.getInvoiceDate2().equals("")){
    		sql.append(" and invoice_date <= convert(datetime,'"+form.getInvoiceDate2()+"')");
    	}
    	if(!form.getManufacturingOrder1().equals("")){
    		sql.append(" and manufacturing_order >='"+form.getManufacturingOrder1()+"' ");
    	}
    	if(!form.getManufacturingOrder2().equals("")){
    		 sql.append(" and manufacturing_order <='"+form.getManufacturingOrder2()+"' ");
    	}
    	if(!form.getInvoiceNumber1().equals("")){
    		sql.append(" and invoice_number >='"+form.getInvoiceNumber1()+"' ");
    	}
    	if(!form.getInvoiceNumber2().equals("")){
    		sql.append(" and invoice_number <='"+form.getInvoiceNumber2()+"' ");
    	}
    	if(!form.getAdvancedInvoice().equals("")){
    		sql.append(" and advanced_invoice = '"+form.getAdvancedInvoice()+"'");
    	}
    	if(!form.getInvoiceFlag().equals("")){
    		sql.append(" and invoice_flag = '"+form.getInvoiceFlag()+"'");
    	}
    	
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			map.put("countNum", CommonTools.null2String(rest.getString("countNum")).equals("0")?"":CommonTools.null2String(rest.getString("countNum")));
				map.put("totalUnitPrice", CommonTools.convertCurrencyRMB(rest.getString("totalUnitPrice")).equals("￥0.00")?"":CommonTools.convertCurrencyRMB(rest.getString("totalUnitPrice")));
				map.put("totalQty", CommonTools.null2String(rest.getString("totalQty")));
				map.put("totalRbm", CommonTools.convertCurrencyRMB(rest.getString("totalRbm")).equals("￥0.00")?"":CommonTools.convertCurrencyRMB(rest.getString("totalRbm")));
				map.put("totalUsd", CommonTools.convertCurrencyRMB(rest.getString("totalUsd")).equals("￥0.00")?"":CommonTools.null2String(rest.getString("totalUsd")));
				map.put("totalEuros", CommonTools.convertCurrencyRMB(rest.getString("totalEuros")).equals("￥0.00")?"":CommonTools.null2String(rest.getString("totalEuros")));
				
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
	public List queryList(F0506ActionForm form) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select invoice_number,invoice_date,a.customers_code, manufacturing_order,");
    	sql.append(" unit_price,qty,total_rbm,total_usd, total_euros,");
        sql.append(" advanced_invoice,invoice_flag,contract_num,b.customers_name,c.name as advanced_invoice_name,d.name as invoice_flag_name ");
        sql.append(" from f_invoicing_production a,c_customers b,b_whether c,b_whether d ");
        sql.append(" where a.customers_code = b.customers_code and a.advanced_invoice = c.code and a.invoice_flag = d.code ");
 
    	if(!form.getContractNum1().equals("")){
    		sql.append(" and a.contract_num >="+form.getContractNum1());
    	}
    	if(!form.getContractNum2().equals("")){
    		sql.append(" and a.contract_num <="+form.getContractNum2());
    	}
    	if(!form.getInvoiceDate1().equals("")){
    		sql.append(" and a.invoice_date >= convert(datetime,'"+form.getInvoiceDate1()+"')");
    	}
    	if(!form.getInvoiceDate2().equals("")){
    		sql.append(" and a.invoice_date <= convert(datetime,'"+form.getInvoiceDate2()+"')");
    	}
    	if(!form.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >='"+form.getManufacturingOrder1()+"' ");
    	}
    	if(!form.getManufacturingOrder2().equals("")){
    		 sql.append(" and a.manufacturing_order <='"+form.getManufacturingOrder2()+"' ");
    	}
    	if(!form.getInvoiceNumber1().equals("")){
    		sql.append(" and a.invoice_number >='"+form.getInvoiceNumber1()+"' ");
    	}
    	if(!form.getInvoiceNumber2().equals("")){
    		sql.append(" and a.invoice_number <='"+form.getInvoiceNumber2()+"' ");
    	}
    	if(!form.getAdvancedInvoice().equals("")){
    		sql.append(" and a.advanced_invoice = '"+form.getAdvancedInvoice()+"'");
    	}
    	if(!form.getInvoiceFlag().equals("")){
    		sql.append(" and a.invoice_flag = '"+form.getInvoiceFlag()+"'");
    	}
    	
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			map.put("invoiceNumber", CommonTools.null2String(rest.getString("invoice_number")));
				map.put("invoiceDate", CommonTools.getDateTime(rest.getDate("invoice_date")));
				map.put("customersCode", CommonTools.null2String(rest.getString("customers_code")));
				map.put("manufacturingOrder", CommonTools.null2String(rest.getString("manufacturing_order")));
				map.put("unitPrice", CommonTools.convertCurrencyRMB(rest.getString("unit_price")));
				map.put("qty", CommonTools.null2String(rest.getString("qty")));
				map.put("totalRbm", CommonTools.convertCurrencyRMB(rest.getString("total_rbm")));
				map.put("totalUsd", CommonTools.null2String(rest.getString("total_usd")));
				map.put("totalEuros", CommonTools.null2String(rest.getString("total_euros")));
				map.put("advancedInvoice", CommonTools.null2String(rest.getString("advanced_invoice")));
				map.put("invoiceFlag", CommonTools.null2String(rest.getString("invoice_flag")));
				map.put("contractNum", CommonTools.null2String(rest.getString("contract_num")));
				map.put("customersName", CommonTools.null2String(rest.getString("customers_name")));
				map.put("advancedInvoiceName", CommonTools.null2String(rest.getString("advanced_invoice_name")));
				map.put("invoiceFlagName", CommonTools.null2String(rest.getString("invoice_flag_name")));
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
}
